[SQL][MS access] Query to find duplicate item in 2 table

Posted by Rico on Stack Overflow See other posts from Stack Overflow or by Rico
Published on 2010-12-28T13:40:05Z Indexed on 2010/12/28 13:54 UTC
Read the original article Hit count: 101

Filed under:

sql

|

query

|

ms-access

I have this table

Antecedent           Consequent
I1                   I2                   
I1                   I1,I2,I3
I1                   I4,I1,I3,I4
I1,I2                I1
I1,I2                I1,I4
I1,I2                I1,I3
I1,I4                I3,I2
I1,I2,I3             I1,I4
I1,I3,I4             I4

AS you can see it's pretty messed up. is there anyway i can remove rows if item in consequent exist in antecedent (in 1 row)

for example: INPUT:

Antecedent           Consequent
I1                   I2                   
I1                   I1,I2,I3    <---- DELETE since I1 exist in antecedent
I1                   I4,I1,I3,I4 <---- DELETE since I1 exist in antecedent
I1,I2                I1          <---- DELETE since I1 exist in antecedent
I1,I2                I1,I4       <---- DELETE since I1 exist in antecedent
I1,I2                I1,I3       <---- DELETE since I1 exist in antecedent
I1,I4                I3,I2       <---- DELETE since I2 exist in antecedent
I1,I2,I3             I1,I4
I1,I3,I4             I4          <---- DELETE since I4 exist in antecedent

OUTPUT:

Antecedent           Consequent
I1                   I2                   
I1,I2,I3             I1,I4

is there anyway i can do that by query?

© Stack Overflow or respective owner

Related posts about sql

SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video

as seen on SQL Authority - Search for 'SQL Authority'
Concatenating string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person by first name and last name. In this video we will see various methods to concatenate the strings. SQL… >>> More
SQL SERVER – Concat Function in SQL Server – SQL Concatenation

as seen on SQL Authority - Search for 'SQL Authority'
Earlier this week, I was delivering Advanced BI training on the subject of “SQL Server 2008 R2″. I had great time delivering the session. During the session, we talked about SQL Server 2010 Denali. Suddenly one of the attendees suggested his displeasure for the product. He said, even though… >>> More
Error with SQL Server Setup 2012 on Windows 2012

as seen on Server Fault - Search for 'Server Fault'
I am trying to install SQL Server on Windows 2012. I was able to finally get the wizard up and running after making some changes on the server, but now it fails no matter what I do with the following error: TITLE: SQL Server Setup failure. SQL Server Setup has encountered the following error: … >>> More
How can I detect which version of SQL (eg SQL 2008 or SQL Azure)

as seen on Stack Overflow - Search for 'Stack Overflow'
I need to detect which version of SQL I am dealing with to perorm various tasks, I need specifically detect if I am on SQL 2008 or SQL Azure. How can I do this with detection code written in SQL? >>> More
Nested SQL Select statement fails on SQL Server 2000, ok on SQL Server 2005

as seen on Stack Overflow - Search for 'Stack Overflow'
Here is the query: INSERT INTO @TempTable SELECT UserID, Name, Address1 = (SELECT TOP 1 [Address] FROM (SELECT TOP 1 [Address] FROM [UserAddress] ua INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID WHERE ua.UserID = u.UserID ORDER BY uo.AddressOrder ASC) q ORDER BY AddressOrder… >>> More

Related posts about query

sort mysql query by filtered query

as seen on Stack Overflow - Search for 'Stack Overflow'
I have two mysql queries: $sql = "SELECT * FROM content WHERE threadName LIKE '%$filter%' ORDER BY lastUpdated desc"; and $sql = "SELECT * FROM content ORDER BY lastUpdated desc"; The end result is to have all rows returned from a particular table 'content' but have those that match the variable… >>> More
How to use PHPunit's Database Test Case Methods of Query to compare with doctrine query

as seen on Stack Overflow - Search for 'Stack Overflow'
How to use PHPunit's Database Test Case Methods of Query to compare with doctrine query i m using phpunit with doctrine framework .. i want to test my doctrine query with phpunit query with using phpunit framework methods.. any buddy with Doctrine Query and PhpUnit Query plz Contact... //Bhavin… >>> More
Converting sql query to EF query - nested query in from

as seen on Stack Overflow - Search for 'Stack Overflow'
Hey guys Just wondering how the following sql query would look in linq for Entity Framework... SELECT KPI.* FROM KeyPerformanceIndicator KPI INNER JOIN ( SELECT SPP.SportProgramPlanId FROM SportProgramPlan PSPP INNER JOIN SportProgramPlan ASPP … >>> More
Sql Query - Limiting query results

as seen on Stack Overflow - Search for 'Stack Overflow'
I am quite certain we cannot use the LIMIT clause for what I want to do - so wanted to find if there are any other ways we can accomplish this. I have a table which captures which user visited which store. Every time a user visits a store, a row is inserted into this table. Some of the fields are shopping_id… >>> More
hql equivalent query to this sql query

as seen on Stack Overflow - Search for 'Stack Overflow'
Select top 1 from <tablename> what is hql query for the above ? >>> More